import java.sql.*;


/**
 * 
 * @author Chris & Matt
 * 
 * A class to help with DB calls and functions
 *
 */
public class DatabaseHelper {
//TODO
//create_table()
//insert()
//etc..
	
	/**
	 * the connection for the DB
	 */
	Connection con;
	
	
	/**
	 * Meta Data about the Result Set
	 */
	ResultSetMetaData rsmd;
	
	/**
	 * the results returned by each query
	 */
	ResultSet rs;
	
	/**
	 * the current item selected from the result set
	 */
	Integer currentItem;
	
	/**
	 * the query to execute on the SQL server
	 */
	PreparedStatement query;
	
	public DatabaseHelper(Connection con, ResultSet rs, Integer currentItem) throws SQLException{
		
		this.con = con;
		this.rs = rs;
		this.currentItem = currentItem;
		this.query = this.con.prepareStatement("");
				
	}
	
	public void create_table(){
		
	}
	
	/**
	 * used when selecting data from table
	 * @param command : the mysql statement to be executed
	 * @return 
	 * @throws SQLException 
	 */
	public ResultSet executeQuery(String command) throws SQLException{
		
		rs = query.executeQuery(command);
		
		if(rs == null){
			System.out.println("RS === NULL");
		}
		else if(command == null){
			System.out.println("Command is null, please enter command");
		}
		
		return rs;
		
	}

	/**
	 * used when altering data in tables
	 * @param command
	 * @throws SQLException
	 */
	public void executeUpdate(String command) throws SQLException{
		
		query.executeUpdate(command);
		
	}
	
	/**
	 * Print the result set of a statement on 'Orders'
	 * @param rs
	 * @throws SQLException
	 */
	public void printResultSetOrders(ResultSet rs) throws SQLException{
		
		System.out.println("Table: " + rs.getMetaData().getTableName(1));
		for  (int i = 1; i<= rs.getMetaData().getColumnCount(); i++){
			System.out.print(rs.getMetaData().getColumnName(i) + "  ");
		}
		
		
		/**
		 * print out all of the result set
		 */
		
		System.out.println("");
		
		while(rs.next()){

			int date = rs.getInt("date");
			String item = rs.getString("item");
			int quantity = rs.getInt("quantity");
			String address = rs.getString("address");
			String paymethod = rs.getString("paymethod");
			
			String s = "   ";
			
			System.out.println(date + s + item + s + quantity + s + address + s + paymethod);
			
			
		}
		System.out.println("");
	}
	
	
	
	public void printResultSetBooks(ResultSet rs) throws SQLException{
		
		System.out.println("Table: " + rs.getMetaData().getTableName(1));
		for  (int i = 1; i<= rs.getMetaData().getColumnCount(); i++){
			System.out.print(rs.getMetaData().getColumnName(i) + "  ");
		}
		
		
		/**
		 * print out all of the result set
		 */
		
		System.out.println("");
		
		while(rs.next()){
			//needs table info to for results;
			int asin = rs.getInt("asin");
			int isbn = rs.getInt("isbn");
			String title = rs.getString("title");
			String author = rs.getString("author");
			String pubDate = rs.getString("pubDate");
			int edition = rs.getInt("edition");
			int price = rs.getInt("price");
			int pagecount = rs.getInt("pagecount");
			String publisher = rs.getString("publisher");
			
			String s = "    ";
			
			System.out.println(asin + s + isbn + s + title + s +  author + s + pubDate + s + edition + s + price + s + pagecount + s + publisher);
			
			
		}
		System.out.println("");
	}
	
	public void printResultSet(ResultSet rs) throws SQLException{
		
		
		System.out.println("Table: " + rs.getMetaData().getTableName(1));
		for  (int i = 1; i<= rs.getMetaData().getColumnCount(); i++){
			System.out.print(rs.getMetaData().getColumnName(i) + "  ");
		}
		
		
		/**
		 * print out all of the result set
		 */
		
		System.out.println("");
		
		while(rs.next()){
			//needs table info to for results;
			int id = rs.getInt("id");
			String fname = rs.getString("fname");
			String lname = rs.getString("lname");
			System.out.println(id + " " + fname + " " + lname);
			
		}
		System.out.println("");
	}
	
	
	/**
	 * Creates a new DB entry Customer with fname, lname and NULL address
	 * 
	 * @param fname
	 * @param lname
	 * @throws SQLException
	 */
	public void insert_into_customers(String fname, String lname) throws SQLException{
		
		query = this.con.prepareStatement("INSERT INTO Customers (fname, lname) VALUES (?,?)");
		query.setString(1, fname);
		query.setString(2, lname);
		query.executeUpdate();

	}
	
	public void insert_into_orders(String item, String address, int quantity, int date, String paymethod) throws SQLException{
		
		query = this.con.prepareStatement("INSERT INTO Orders (item, address, quantity, date, paymethod) VALUES (?, ?, ?, ?, ?)");
		query.setString(1, item);
		query.setString(2, address);
		query.setInt(3, quantity);
		query.setInt(4, date);
		query.setString(5, paymethod);
		query.executeUpdate();
		
	}
	
	/**
	 * Creates the table to Store Book relations
	 * @throws SQLException
	 */
	public void create_table_books() throws SQLException{
	
		query = this.con.prepareStatement("CREATE TABLE Books" +
				"(" +
				"asin varchar(20) primary key," +
				"isbn varchar(30)," +
				"title varchar(1000)," +
				"author varchar(1000)," +				
				"price int," +
				"bindingtype varchar(30)," +
				"pagecount int," +
				"publisher varchar(100)," +				
				"edition int," +
				"pubDate varchar(20)" +
				");");
		
		query.executeUpdate();
		
		System.out.println("Created Table Books!~~");
	}
	
	/**
	 * Create the Table for the customers
	 * TODO
	 * ADD 1NF for:
	 * 	-address
	 * 	-credit card
	 * @throws SQLException
	 */
	public void create_table_customers() throws SQLException{
		
		query = this.con.prepareStatement("CREATE TABLE Customers" +
				"(" +
				"id varchar(20) primary key," +
				"fname varchar(20)," +
				"lname varchar(20)" +
				");");
		
		
		query.executeUpdate();
		
		System.out.println("Created Table Customers!~~");
	}
	
	
	
	
	/**
	 * create the table for reviews
	 * TODO
	 * we must use multiple keys ie (customer, item) key
	 * this table needs work
	 * @throws SQLException
	 */
	public void create_table_reviews() throws SQLException{
		
		query = this.con.prepareStatement("CREATE TABLE Reviews(" +
				"asin varchar(20)," +
				"custid varchar(20)," +
				"rating integer," +
				"entered date," +
				"reviewtext text," +
				"primary key(asin, custid)" +
				");");
		query.executeUpdate();
		
		System.out.println("Created Table Reviews!~~");
		
	}
	
	/**
	 * Create the table for Orders
	 * @throws SQLException 
	 */
	public void create_table_orders() throws SQLException{
		query = this.con.prepareStatement("CREATE TABLE Orders(" +
				"date int," +
				"item varchar(20)," +
				"quantity int," +
				"address varchar(20)," +
				"paymethod varchar(20)," +
				"primary key (item, address)" +
				");");
		query.executeUpdate();
		
		System.out.println("Created Table Orders!~~");
		
	}
	
	/**
	 * Displays the Books table
	 * @throws SQLException
	 */
	public void displayBooks() throws SQLException{
		rs = this.executeQuery("SELECT * FROM Books;");
		printResultSetBooks(rs);
	}
	
	/**
	 * Displays the CUstomers table
	 * @throws SQLException
	 */
	public void displayCustomers() throws SQLException{
		this.executeQuery("SELECT * FROM Customers;");
		printResultSet(rs);
	}
	
	/**
	 * display all the Reviews
	 */
	public void displayReviews() throws SQLException{
		this.executeQuery("SELECT * FROM Reviews;");
	}
	
	/**
	 * display all the Orders
	 */
	public void displayOrders() throws SQLException{
		this.executeQuery("SELECT * FROM Orders;");
		printResultSetOrders(rs);
	}

	/**
	 * Search books where "category = value" (ie author = "smith")
	 * @param category
	 * @param value
	 * @throws SQLException
	 */
	public ResultSet searchBooksBy(String category, String value) throws SQLException{
		query = this.con.prepareStatement("Select * FROM Books WHERE " + category + "=?;");
		query.setString(1, value);
		System.out.println(query);
		rs = query.executeQuery();
		printResultSetBooks(rs);
		return rs;
		
	}
	
	
	public void select(String item){
		if(this.rs == null){
			
		}
		
	
	}

	public void init() throws SQLException{
		
		create_table_books();
		create_table_customers();
		create_table_reviews();
		create_table_orders();
		
	}
	
	
}
